<?

//Initiate Authenticate
SecurityRedirect ();

//initiate Vars
define("PRIMARY_KEY","nobkt");
$KEY[]         = array("PRIMARY_KEY"=>PRIMARY_KEY);
$FileName      = "ais_closing_closeyear.php";
$FileTemplate  = "ais_closing_closeyear_list.html";
$TableName     = "ais_advance";
$SubHeaderTitle= "Closing - Closing Year";
$PageSize      = 10;
$HTMLGridList  = "ClosingPeriod";

//************ FORM ACTION **************
if (GetParam("Action","")=="Closing") {
    if (!CheckClosingYear(GetParam("periodYear",""))) {
        print "
               <script>
                alert('Month 12 on Year ".GetParam("periodYear","")." still open, Closing Failed!');
                self.location.replace('$FileName');
               </script>
               ";
    } else {
        ClosingYearCOA(GetParam("periodYear",""));

        print "
               <script>
                alert('Closing Success!');
                self.location.replace('$FileName');
               </script>

               ";
    }
}

//******* SHOW FORM CONTENT *********
$TBS = new clsTinyButStrong;
$TBS->LoadTemplate($MOD_TEMPLATE_DIR."/".$FileTemplate);
$TBS->MergeBlock("monthformblk",GetArrayMonth());
$ArrayYear[0]["year"] = date("Y");
$ArrayYear[1]["year"] = date("Y")-1;

$TBS->MergeBlock("yearformblk",$ArrayYear);
$TBS->Show();

function CheckClosingYear($Year) {
  global $DBConnection;
  $SQL = "SELECT status FROM ais_history_ledger WHERE year='$Year' AND month='12' ";
  if ($DBConnection->dbc->get_var($SQL)=="Close") return true;
  else return false;
}

function Get_salrl() {
  global $DBConnection;
  $SQL = "SELECT salrl FROM tblprofile";
  return $DBConnection->dbc->get_var($SQL);
}

function Get_salrlytd() {
  global $DBConnection;
  $SQL = "SELECT salrlytd FROM tblprofile";
  return $DBConnection->dbc->get_var($SQL);
}


function ClosingYearCOA($Year) {
  global $DBConnection;
    $NextYear = $Year+1;


    	$SQL = "DROP TABLE IF EXISTS `tblcoatmp`";
    	$DBConnection->dbc->query($SQL);
	
        $SQL = SQLDuplicateStructure("tblcoa", "tblcoatmp", "0", "","skada_id");
        $DBConnection->dbc->query($SQL);
        
        $SQL = "INSERT INTO tblcoatmp  SELECT ".GetColumnStruct("tblcoa", "skada_id")." FROM tblcoa WHERE ledger='$Year' ";
        $DBConnection->dbc->query($SQL);

        $SQL = "UPDATE tblcoatmp SET ledger='$NextYear' ";
        $DBConnection->dbc->query($SQL);
        
        $SQL = "UPDATE tblcoatmp SET
                        awal  = '0',mutd01 = '0',mutk01 = '0',
                        aku01 = '0',mutd02 = '0',mutk02 = '0',
                        aku02 = '0',mutd03 = '0',mutk03 = '0',
                        aku03 = '0',mutd04 = '0',mutk04 = '0',
                        aku04 = '0',mutd05 = '0',mutk05 = '0',
                        aku05 = '0',mutd06 = '0',mutk06 = '0',
                        aku06 = '0',mutd07 = '0',mutk07 = '0',
                        aku07 = '0',mutd08 = '0',mutk08 = '0',
                        aku08 = '0',mutd09 = '0',mutk09 = '0',
                        aku09 = '0',mutd10 = '0',mutk10 = '0',
                        aku10 = '0',mutd11 = '0',mutk11 = '0',
                        aku11 = '0',mutd12 = '0',mutk12 = '0',
                        aku12 = '0',mutd13 = '0',mutk13 = '0',
                        aku13 = '0',akhir = '0',
                        bgt01 = '0',bgt02 = '0',bgt03 = '0',
                        bgt04 = '0',bgt05 = '0',bgt06 = '0',
                        bgt07 = '0',bgt08 = '0',bgt09 = '0',
                        bgt10 = '0',bgt11 = '0',bgt12 = '0'
                    WHERE
                        ledger = $NextYear AND ktrasl='".GetOfficeID()."'
        ";
        $DBConnection->dbc->query($SQL);

     //UPDATE SELAEN
      $SQL = "SELECT skada,aku12,mutd13,mutk13
              FROM tblcoa
              LEFT JOIN tblcoagroup ON tblcoa.grup=tblcoagroup.group_id
              WHERE NOT (coatype = 'REVENUES' OR coatype = 'EXPENSES' OR coatype = 'OTHER EXPENSES') AND
              ledger='$Year'
              ";
      $MyResult = $DBConnection->dbc->get_results($SQL,ARRAY_A);
//      $DBConnection->dbc->debug();

      if ($MyResult) {
        foreach ($MyResult as $RowResult) {
          $Awal = $RowResult["aku12"]+$RowResult["mutd13"]-$RowResult["mutk13"];
          $SQL = "UPDATE tblcoatmp SET awal='$Awal' WHERE skada='".$RowResult["skada"]."' AND ledger='$NextYear' AND ktrasl='".GetOfficeID()."'";
          $DBConnection->dbc->query($SQL);
        }
      }

      //UPDATE REVENUE RETAIN EARNING
      $SQL = "SELECT sum(aku12)+sum(mutd13)-sum(mutk13) as awal
              FROM tblcoa
              LEFT JOIN tblcoagroup ON tblcoa.grup=tblcoagroup.group_id
              WHERE (coatype = 'REVENUES' OR coatype = 'EXPENSES' OR coatype = 'OTHER EXPENSES') AND
              ledger='$Year'
              ";
      $MyResult = $DBConnection->dbc->get_row($SQL);

      $SQL = "SELECT aku12+mutd13-mutk13 as aku_last
              FROM tblcoa
              WHERE skada='".Get_salrl()."' AND
              ledger='$Year'
              ";
      $RetainEarning = $DBConnection->dbc->get_var($SQL);
      $TotalRetEarn  = $MyResult->awal+$RetainEarning;
//      print $MyResult->awal."+".$RetainEarning;die();

      $SQL  = "UPDATE tblcoatmp SET awal='$TotalRetEarn' WHERE skada='".Get_salrl()."' AND ledger='$NextYear' AND ktrasl='".GetOfficeID()."' ";
      $DBConnection->dbc->query($SQL);

     //Zeroing YTD
      $SQL  = "UPDATE tblcoatmp SET awal='0' WHERE skada='".Get_salrlytd()."' AND ledger='$NextYear' AND ktrasl='".GetOfficeID()."' ";
      $DBConnection->dbc->query($SQL);

      $SQL = "SELECT COUNT(*) FROM tblcoa WHERE ledger='$NextYear' AND ktrasl='".GetOfficeID()."'";
      if ($DBConnection->dbc->get_var($SQL)) {
        $SQL = "DELETE FROM tblcoa WHERE ledger='$NextYear' AND ktrasl='".GetOfficeID()."'";
        $DBConnection->dbc->query($SQL);
      }
      
      $SQL = "INSERT INTO tblcoa (".GetColumnStruct("tblcoa", "skada_id").") SELECT * FROM tblcoatmp ";
      $DBConnection->dbc->query($SQL);

	// Add by Chandra
	$_sqlSelisih = "(SUM(if(dk='D',jumlah,0))-SUM(if(dk='K',jumlah,0)))";
	$SQL = "INSERT INTO tblsaldodollar_temp
		 SELECT skada, '$NextYear' tahun,
		 SUM(if(dk='D',jumlah,0)) totaldebet, 
		 SUM(if(dk='K',jumlah,0)) totalkredit, 
		 $_sqlSelisih selisih, 
		 (CAST( (SELECT awal FROM tblsaldocb sld WHERE sld.skada = m.skada AND year = '2008') AS DECIMAL)+$_sqlSelisih) jumlah
		 FROM ais_memorial m WHERE YEAR(tglbkt) <= $Year AND curr = 'USD' 
		 AND (m.skada LIKE '00102%' OR m.skada LIKE '00002%') GROUP BY m.skada";
//die($SQL);
	$DBConnection->dbc->query($SQL);
}

?>
